﻿Option Explicit On

Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop
Imports System.Threading
Public Class frmrpt_depotgrapth

    Private Sub frmrpt_depotgrapth_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim respone As Object
        Dim mysql As MySqlConnection = main_form.mysqlconection

        Chart1.Series.Dispose()

        Chart1.Series.Clear()

        Chart1.Series.Add("ตู้ทั้งหมด")
        Chart1.Series.Add("ตู้ใช้งานได้")
        Chart1.Series.Add("ตู้ซ่อม")
        Chart1.Series.Add("ตู้ล้าง")

        Chart1.Series("ตู้ทั้งหมด").IsValueShownAsLabel = True
        Chart1.Series("ตู้ใช้งานได้").IsValueShownAsLabel = True
        Chart1.Series("ตู้ซ่อม").IsValueShownAsLabel = True
        Chart1.Series("ตู้ล้าง").IsValueShownAsLabel = True

        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        For i = CInt(frmrpt_depotdetail.voystart) To CInt(frmrpt_depotdetail.voyEnd)
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            mySqlCommand.CommandText = "Select VOYAGEID,VOYVESNAMEN,VOYDATEEN,VOYTIMEHHMMNN,COUNT(case when ctnstatdepot =1 then ctnmain.CTNMAINID else NULL end) as ctn_all ,COUNT(case when ctnstatdepot =2 then ctnmain.CTNMAINID else NULL end) as ctn_wt,COUNT(case when ctnstatdepot =3 then ctnmain.CTNMAINID else NULL end) as ctn_dm, COUNT(case when ctnstatdepot =0 then ctnmain.CTNMAINID else NULL end) as ctn_use from voyage join ctnmain on voyage.VOYAGEID = ctnmain.CTNVOYN join ctndepot on ctnmain.CTNMAINID = ctndepot.CTNID where  VOYAGEID = '" & i.ToString & "'   GROUP BY VOYAGEID ;"        ' mySqlCommand.CommandText = 
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())

                    Chart1.Series("ตู้ทั้งหมด").Points.AddXY("VOY" + mySqlReader("VOYAGEID").ToString, mySqlReader("ctn_all"))
                   
                    Chart1.Series("ตู้ใช้งานได้").Points.AddXY("VOY" + mySqlReader("VOYAGEID").ToString, mySqlReader("ctn_use"))
                    Chart1.Series("ตู้ซ่อม").Points.AddXY("VOY" + mySqlReader("VOYAGEID").ToString, mySqlReader("ctn_dm"))
                    Chart1.Series("ตู้ล้าง").Points.AddXY("VOY" + mySqlReader("VOYAGEID").ToString, mySqlReader("ctn_wt"))


                End While
            Catch ex As Exception

            End Try
        Next
    End Sub
End Class